package dao.daocontrollers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import org.apache.log4j.Logger;

import api.entitites.IOrder;
import api.entitites.OrderStatus;
import api.workerswithdb.IDAOGarage;
import api.workerswithdb.IDAOMaster;
import api.workerswithdb.IDAOOrder;
import api.workerswithdb.SortOrdersBy;

public class DAOOrder extends GenericDAO implements IDAOOrder {

	private static final String SORT_ALL_ORDERS = "select * from orders order by ?";
	private static final String SORT_MADING_ORDERS = "select * from orders where status_order is null order by ?";
	private static final String SORT_ORDERS_BY_PERIOD = " select * from orders where begining_date between ? and ? and status_order=? order by ?";
	private static final String GET_MASTER_BY_ORDER = "select master_name from orders join masters using(id_master) where number_order=? ";
	private static final String GET_NEXT_FREE_DATE = "select min(deadline_date) from orders where status_order is null";
	private static final String ORDER_NUMBER = "select max(number_order) from orders";
	private static final String ORDER_INSERT="insert into orders(number_order,status_order,filling_date, begining_date,deadline_date,price, id_master, id_garage) values(?,?,?,?,?,?,?,?)";
	private static final String READ_ORDER="select * from orders where number_order=?";
	private static final String SHIFT_ORDER_DATE="update orders set deadline_date=? where id_order=?";
	private static final String MARK_ORDER= "update orders set status_order=? where id_order=?";
	private static final String GET_ID_ORDER_MASTER_GARAGE="select id_order,id_master,id_garage from orders where number_order=?";
	private static final String ID_ORDER= "select id_order from orders where number_order =?";



	private Logger log = Logger.getLogger(DAOOrder.class);
	private IDAOGarage daoGarage=new DAOGarage();
	private IDAOMaster daoMaster=new DAOMaster();

	

	public Boolean insertOrderToDB(IOrder order) {
		Boolean answer = false;
		Connection connection=super.connectorTDB.getConnection();
		try {
			connection.setAutoCommit(false);
			connection.setTransactionIsolation(4);
			java.sql.Date fillingDate = EntitiesGenerator.conversionDate(order
					.getFillingDate());
			java.sql.Date beginingDate = EntitiesGenerator.conversionDate(order
					.getBeginingDate());
			java.sql.Date deadlineDate = EntitiesGenerator.conversionDate(order
					.getDeadlineDate());		
			super.insert(ORDER_INSERT,order.getNumberOrder(), order.getOrderStatus(),
					fillingDate, beginingDate, deadlineDate, order.getPrice(),
					order.getIdMaster(), order.getIdGarage());
			daoGarage.updateGarage(connection,false, order.getIdGarage());
			daoMaster.updateMaster(connection,false, order.getIdMaster());
			connection.commit();
			connection.close();
			answer = true;
		} catch (Exception e) {
			log.error(DBMessanger.ORD_EXISTS);
			try{
			connection.rollback();
			connection.close();
			}catch(SQLException sqle){
				log.error(DBMessanger.ERROR);
			}
		}
		return answer;
	}

	public IOrder getOrderFromDb(Integer numberOrder) {
		ResultSet orderFromDbResult;
		try {
			orderFromDbResult = super.read(READ_ORDER,numberOrder);
			return EntitiesGenerator.parserOrder(orderFromDbResult);
		} catch (SQLException sqle) {
			log.error(DBMessanger.ORD_NOT_EXISTS);
			return null;
		}
	}

	public Integer getIdOrder(Integer orderNumber) {
		try {
			Integer orderId = super.getObjectId(ID_ORDER,orderNumber);
			return orderId;
		} catch (SQLException sqle) {
			log.error(DBMessanger.ORD_NOT_EXISTS);
			return null;
		}
	}

	public ArrayList<IOrder> sortAllOrders(SortOrdersBy sortOrdersBy) {

		PreparedStatement sortAllOrders=null;
		ResultSet sortAllOrdersResult;
		Connection connection=super.connectorTDB.getConnection();
		try {
			sortAllOrders = connection.prepareStatement(SORT_ALL_ORDERS);
			String sort=sortOrdersBy.toString().toLowerCase();
			sortAllOrders.setString(1, sort);
			sortAllOrdersResult = sortAllOrders.executeQuery();
			
			return EntitiesGenerator.parserOrders(sortAllOrdersResult);
		} catch (SQLException sqle) {
			log.error(DBMessanger.ERROR);
			return null;
		}finally{
			try{
			sortAllOrders.close();
			}catch(SQLException sqle){
				log.error(DBMessanger.ERROR);

			}
			
		}
	}

	public ArrayList<IOrder> sortMadingOrders(SortOrdersBy sortOrdersBy) {
		PreparedStatement sortMadingOrders=null;
		ResultSet sortMadingOrdersResult;
		Connection connection=super.connectorTDB.getConnection();
		try {
			sortMadingOrders = connection.prepareStatement(SORT_MADING_ORDERS);
			sortMadingOrders.setString(1, sortOrdersBy.toString().toLowerCase());
			sortMadingOrdersResult = sortMadingOrders.executeQuery();
			return EntitiesGenerator.parserOrders(sortMadingOrdersResult);
		} catch (SQLException sqle) {
			log.error(DBMessanger.ERROR);
			return null;
		}finally{
			try{				
				sortMadingOrders.close();
				connection.close();
			}catch (SQLException sqle) {
				log.error(DBMessanger.ERROR);
			}
		}

	}

	public ArrayList<IOrder> sortOrdersByPeriod(Date begin, Date end,
			SortOrdersBy sortOrdersBy, OrderStatus orderStatus) {
		PreparedStatement sortOrdersByPeriod=null;
		ResultSet sortOrdersByPeriodResult;
		Connection connection=super.connectorTDB.getConnection();	
		try {
			sortOrdersByPeriod = connection
					.prepareStatement(SORT_ORDERS_BY_PERIOD);

			java.sql.Date date_begin = EntitiesGenerator.conversionDate(begin);
			java.sql.Date date_end = EntitiesGenerator.conversionDate(end);

			sortOrdersByPeriod.setDate(1, date_begin);
			sortOrdersByPeriod.setDate(2, date_end);
			sortOrdersByPeriod.setString(3, orderStatus.toString());
			sortOrdersByPeriod.setString(4, sortOrdersBy.toString().toLowerCase());
			sortOrdersByPeriodResult = sortOrdersByPeriod.executeQuery();
			return EntitiesGenerator.parserOrders(sortOrdersByPeriodResult);
		} catch (SQLException sqle) {
			log.error(DBMessanger.ERROR);
			return null;
		}finally{
			try{				
				sortOrdersByPeriod.close();
				connection.close();				
			}catch (SQLException sqle) {
				log.error(DBMessanger.ERROR);
			}
		}
	}

	public String getMasterByOrder(Integer numberOrder) {
		PreparedStatement getMasterByOrder;
		ResultSet getMasterByOrderResult;
		String masterName = null;
		Connection connection=super.connectorTDB.getConnection();	
		try {
			getMasterByOrder = connection.prepareStatement(GET_MASTER_BY_ORDER);
			getMasterByOrder.setInt(1, numberOrder);
			getMasterByOrderResult = getMasterByOrder.executeQuery();
			while (getMasterByOrderResult.next()) {
				masterName = getMasterByOrderResult.getString(1);
			}
			getMasterByOrder.close();
			getMasterByOrderResult.close();
			connection.close();
		} catch (SQLException sqle) {
			log.error(DBMessanger.ORD_NOT_EXISTS);
		}
		return masterName;
	}

	public Date getNextFreeDate() {
		PreparedStatement getNextFreeDate;
		ResultSet getNextFreeDateResult;
		Date date = null;
		Connection connection=super.connectorTDB.getConnection();
		try {
			getNextFreeDate = connection.prepareStatement(GET_NEXT_FREE_DATE);
			getNextFreeDateResult = getNextFreeDate.executeQuery();
			while (getNextFreeDateResult.next()) {
				date = getNextFreeDateResult.getDate(1);
			}
			getNextFreeDate.close();
			getNextFreeDateResult.close();
			connection.close();
		} catch (SQLException sqle) {
			log.error(DBMessanger.ERROR);
		}
		return date;
	}

	public Boolean shiftDeadlineDate(Date newDate, Integer numberOrder) {
		Boolean answer = false;
		try {
			Connection connection=super.connectorTDB.getConnection();
			super.update(connection,SHIFT_ORDER_DATE,newDate, numberOrder);
			answer = true;
		} catch (Exception e) {
			log.error(DBMessanger.ERROR);
		}
		return answer;

	}

	public Boolean markOrder(Integer orderNumber, OrderStatus orderStatus) {
		Boolean answer = false;
		Connection connection=super.connectorTDB.getConnection();
		try {
			PreparedStatement id;
			ResultSet idResult;
			Integer idMaster=0;
			Integer idOrder=0;
			Integer idGarage=0;
			connection.setAutoCommit(false);
			connection.setTransactionIsolation(4);
			id=connection.prepareStatement(GET_ID_ORDER_MASTER_GARAGE);
			id.setInt(1, orderNumber);
			idResult=id.executeQuery();
			while(idResult.next()){
				idOrder=idResult.getInt(1);
				idMaster=idResult.getInt(2);
				idGarage=idResult.getInt(3);
			}
			super.update(connection,MARK_ORDER,orderStatus.toString(),idOrder);
			daoGarage.updateGarage(connection,false, idGarage);
			daoMaster.updateMaster(connection,false, idMaster);
			connection.commit();			
			answer = true;
			connection.close();
			id.close();
			idResult.close();
		} catch (Exception e) {
			log.error(DBMessanger.ERROR);
			try{
			connection.rollback();
			connection.close();
			}catch(SQLException sqle){
				log.error(DBMessanger.ERROR);
			}
		}
		return answer;
	}

	public Integer cloneOrder(Integer numberOrder) {

		PreparedStatement orderNumber;
		ResultSet orderNumberResult;
		Integer cloneNumber = 0;
		Connection connection=super.connectorTDB.getConnection();
		try {
			orderNumber = connection.prepareStatement(ORDER_NUMBER);
			connection.setAutoCommit(false);
			connection.setTransactionIsolation(4);
			IOrder order = getOrderFromDb(numberOrder).cloneOrder();
			orderNumberResult = orderNumber.executeQuery();
			while (orderNumberResult.next()) {
				cloneNumber = orderNumberResult.getInt(1);
			}
			cloneNumber += 1;
			order.setNumberOrder(cloneNumber);
			insertOrderToDB(order);
			connection.commit();
			orderNumber.close();
			orderNumberResult.close();
			connection.close();
		} catch (Exception e) {
			log.error(DBMessanger.EXCEPTION_TRANSACTION);
			try {
				connection.rollback();
				connection.close();
			} catch (SQLException e1) {
				log.error(DBMessanger.ERROR);
			}
		}
		return cloneNumber;
	}
}
